﻿/*********************************************************
 * 开发人员：小兵
 * 创建时间：2012-10-18 11:15:02
 * 描述说明：
 * 
 * 更改历史：
 * 
 * *******************************************************/
namespace Mozlite
{
    using System;
    using System.Web;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Mozlite.Data;
    using System.Web.Routing;
    using System.Xml;
    using System.Transactions;

    /// <summary>
    /// 数据库执行方法类。
    /// </summary>
    public static class Database
    {
        #region helpers
        private static IParameter[] ToArray(this object parameters)
        {
            if(parameters == null)
                return null;
            var list = new List<IParameter>();
            foreach(var parameter in new RouteValueDictionary(parameters))
            {
                list.Add(SqlHelper.CreateParameter("@" + parameter.Key, parameter.Value));
            }
            return list.ToArray();
        }

        private static ISqlHelper sqlHelper;
        /// <summary>
        /// 数据库方法接口实例对象。
        /// </summary>
        private static ISqlHelper SqlHelper
        {
            get
            {
                if(sqlHelper == null)
                {
                    try
                    {
                        sqlHelper = DataHelper.CreateSqlHelper();
                    }
                    catch { }
                }
                return sqlHelper;
            }
        }
        /// <summary>
        /// 格式化ID集合，如果包含多个ID则使用IN语句，否则直接使用“=”。
        /// </summary>
        /// <param name="ids">ID集合，多个ID使用“,”分割。</param>
        /// <returns>返回格式化后的语句。</returns>
        public static string FormatIDs(this string ids)
        {
            if(ids.IndexOf(',') == -1)
                return " = " + ids;
            return " IN({0})".FormatWith(ids);
        }
        /// <summary>
        /// 开启一个事务处理。
        /// </summary>
        /// <param name="action">执行的方法。</param>
        public static void BeginTransaction(Action<IDbTransaction> action) {
            var transaction = SqlHelper.BeginTransaction();
            try
            {
                action(transaction);
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
            }
        }
        /// <summary>
        /// 开启一个事务处理。
        /// </summary>
        /// <typeparam name="T">返回类型。</typeparam>
        /// <param name="func">执行的方法。</param>
        /// <param name="defaultValue">默认值，回滚后返回的值。</param>
        /// <returns>返回执行结果。</returns>
        public static T BeginTransaction<T>(Func<IDbTransaction, T> func, T defaultValue = default(T))
        {
            var transaction = SqlHelper.BeginTransaction();
            try
            {
                T value = func(transaction);
                transaction.Commit();
                return value;
            }
            catch
            {
                transaction.Rollback();
            }
            return defaultValue;
        }
        /// <summary>
        /// 启动事务执行方法。
        /// </summary>
        /// <param name="action">执行的方法。</param>
        public static void BeginTransaction(Action action)
        {
            using(var transaction = new TransactionScope(TransactionScopeOption.RequiresNew)) {
                action();
            }
        }
        /// <summary>
        /// 启动事务执行方法。
        /// </summary>
        /// <typeparam name="T">返回类型。</typeparam>
        /// <param name="func">执行的方法。</param>
        /// <returns>返回执行结果。</returns>
        public static T BeginTransaction<T>(Func<T> func)
        {
            using(var transaction = new TransactionScope(TransactionScopeOption.RequiresNew))
            {
               return func();
            }
        }
        #endregion

        #region common
        /// <summary>
        /// 执行命令语句，并返回是否有影响的行数。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回是否有影响的行数。</returns>
        public static bool ExecuteNonQuery(string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteNonQuery(commandText, parameters.ToArray()) > 0;
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public static IDataReader ExecuteReader(string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteReader(commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <typeparam name="T">实例类型。</typeparam>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <param name="serializableFieldName">序列化列名称。</param>
        /// <param name="defaultValue">默认值。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public static T ExecuteReader<T>(string commandText, object parameters = null, string serializableFieldName = null, T defaultValue = default(T)) where T : class,new()
        {
            T value = defaultValue;
            using (var reader = SqlHelper.ExecuteReader(commandText, parameters.ToArray()))
            {
                if (reader.Read())
                    value = reader.As<T>(serializableFieldName);
                reader.Close();
            }
            return value;
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public static T ExecuteScalar<T>(string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteScalar<T>(commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public static XmlReader ExecuteXmlReader(string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteXmlReader(commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回是否有影响的行数。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回是否有影响的行数。</returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteNonQuery(commandType, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public static IDataReader ExecuteReader(CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteReader(commandType, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public static T ExecuteScalar<T>(CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteScalar<T>(commandType, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public static XmlReader ExecuteXmlReader(CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteXmlReader(commandType, commandText, parameters.ToArray());
        }
        #endregion

        #region transactions
        /// <summary>
        /// 执行命令语句，并返回是否有影响的行数。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回是否有影响的行数。</returns>
        public static bool ExecuteNonQuery(this IDbTransaction transaction, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteNonQuery(transaction, commandText, parameters.ToArray()) > 0;
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public static IDataReader ExecuteReader(this IDbTransaction transaction, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteReader(transaction, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public static T ExecuteScalar<T>(this IDbTransaction transaction, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteScalar<T>(transaction, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public static XmlReader ExecuteXmlReader(this IDbTransaction transaction, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteXmlReader(transaction, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回是否有影响的行数。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回是否有影响的行数。</returns>
        public static bool ExecuteNonQuery(this IDbTransaction transaction, CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteNonQuery(transaction, commandType, commandText, parameters.ToArray()) > 0;
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public static IDataReader ExecuteReader(this IDbTransaction transaction, CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteReader(transaction, commandType, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回结果实例对象。
        /// </summary>
        /// <typeparam name="T">返回结果类型。</typeparam>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回结果实例对象。</returns>
        public static T ExecuteScalar<T>(this IDbTransaction transaction, CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteScalar<T>(transaction, commandType, commandText, parameters.ToArray());
        }

        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Xml.XmlReader"/>实例对象。
        /// </summary>
        /// <param name="transaction">事务处理实例对象。</param>
        /// <param name="commandType">命令类型。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <returns>返回<see cref="T:System.Xml.XmlReader"/>实例对象。</returns>
        public static XmlReader ExecuteXmlReader(this IDbTransaction transaction, CommandType commandType, string commandText, object parameters = null)
        {
            return SqlHelper.ExecuteXmlReader(transaction, commandType, commandText, parameters.ToArray());
        }
        #endregion

        #region pager and load
        /// <summary>
        /// 分页查询，并返回总数量。
        /// </summary>
        /// <param name="pageIndex">页码，从1开始。</param>
        /// <param name="pageSize">每页显示记录数。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数。</param>
        /// <param name="size">返回总记录数。</param>
        /// <returns>返回数据列表。</returns>
        public static List<T> Fetch<T>(int pageIndex, int pageSize, string commandText, object parameters, out int size) where T : class,new()
        {
            return SqlHelper.Fetch<T>(pageIndex, pageSize, commandText, out size, parameters.ToArray());
        }
        /// <summary>
        /// 分页查询，并返回总数量。
        /// </summary>
        /// <param name="pageIndex">页码，从1开始。</param>
        /// <param name="pageSize">每页显示记录数。</param>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="size">返回总记录数。</param>
        /// <returns>返回数据列表。</returns>
        public static List<T> Fetch<T>(int pageIndex, int pageSize, string commandText, out int size) where T : class,new()
        {
            return Fetch<T>(pageIndex, pageSize, commandText, null, out size);
        }
        /// <summary>
        /// 执行命令语句，并返回<see cref="T:System.Data.IDataReader"/>实例对象。
        /// </summary>
        /// <typeparam name="T">实例类型。</typeparam>
        /// <param name="commandText">SQL语句。</param>
        /// <param name="parameters">参数数组。</param>
        /// <param name="serializableFieldName">序列化列名称。</param>
        /// <returns>返回<see cref="T:System.Data.IDataReader"/>实例对象。</returns>
        public static List<T> Fetch<T>(string commandText, object parameters = null, string serializableFieldName = null) where T : class,new()
        {
            var list = new List<T>();
            using(var reader = SqlHelper.ExecuteReader(commandText, parameters.ToArray()))
            {
                while(reader.Read())
                    list.Add(reader.As<T>(serializableFieldName));
                reader.Close();
            }
            return list;
        }
        #endregion

        #region sortorder
        /// <summary>
        /// 移动方法（上移和下移）。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <param name="primaryKey">主键列名。</param>
        /// <param name="primaryValue">主键值。</param>
        /// <param name="orderby">排序规则。</param>
        /// <param name="isDown">是否下移。</param>
        /// <param name="where">排序条件语句。</param>
        /// <param name="args">参数。</param>
        /// <returns>返回执行结果。</returns>
        public static bool Sorted(string tableName, string primaryKey, int primaryValue, string orderby, bool isDown, string where, object parameters = null)
        {
            Guard.IsNotNullOrEmpty(tableName, "tableName");
            Guard.IsNotNullOrEmpty(primaryKey, "primaryKey");
            Guard.IsNotNullOrEmpty(where, "where");
            int cid = 0, corder = 0, tid = 0, torder = 0;
            using (var reader = ExecuteReader("SELECT [{0}], SortOrder FROM {1} WHERE [{0}] = @PrimaryValue;".FormatWith(primaryKey, tableName), new { PrimaryValue = primaryValue }))
            {
                if (reader.Read())
                {
                    cid = reader.GetInt32(0);
                    corder = reader.GetInt32(1);
                }
                reader.Close();
            }
            if (cid == 0)
            {
                return false;
            }
            if (where != null)
                where = where + " AND ";
            using (var reader = ExecuteReader("SELECT TOP(1) [{0}], SortOrder FROM {1} WHERE {2}SortOrder {4} {5} ORDER BY {3} {6};".FormatWith(primaryKey, tableName, where, orderby, isDown ? ">" : "<", corder, isDown ? "ASC" : "DESC"), parameters))
            {
                if (reader.Read())
                {
                    tid = reader.GetInt32(0);
                    torder = reader.GetInt32(1);
                }
                reader.Close();
            }
            if (tid == 0)
            {
                return false;
            }
            return BeginTransaction<bool>(transaction =>
            {
                transaction.ExecuteNonQuery(@"UPDATE {1} SET SortOrder = {2} WHERE [{0}] = {3};".FormatWith(primaryKey, tableName, corder, tid));
                transaction.ExecuteNonQuery(@"UPDATE {1} SET SortOrder = {2} WHERE [{0}] = {3};".FormatWith(primaryKey, tableName, torder, cid));
                return true;
            });
        }
        /// <summary>
        /// 获取当前排序码。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <param name="where">条件语句。</param>
        /// <param name="parameters">参数。</param>
        /// <returns>返回排序码。</returns>
        public static int GetSortOrder(string tableName, string where, object parameters = null)
        {
            Guard.IsNotNullOrEmpty(tableName, "tableName");
            Guard.IsNotNullOrEmpty(where, "where");
            return ExecuteScalar<int>("SELECT MAX(SortOrder) FROM {0} WHERE {1}".FormatWith(tableName, where), parameters) + 1;
        }
        #endregion
    }
}
